package model;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.SessionFactory;

import util.hibernate.HibernateUtil;

import databean.Tokbox;

public class TokboxDAO {
	
	
	
	public TokboxDAO() {

	}
	
    
    public void create(Tokbox tokbox) {
        
    	org.hibernate.classic.Session session = null;
		
		try {
			
			SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
			session = sessionFactory.openSession();
			
			Connection connection = session.connection();
			Statement statement = connection.createStatement();
			ResultSet result;

			//checking if same entry is already in database
			String query = "select tokboxID from tokbox where confName='"+tokbox.getConfName()+"'";
			result = statement.executeQuery(query);
			
			//yes - ignore; no - add a new email record
			if (result.next() == true) {
				//ignore
			} else {
				//start inserting
				org.hibernate.Transaction tx = session.beginTransaction();
	        	session.saveOrUpdate(tokbox);
				tx.commit();
			}

			statement.close();
			result.close();
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			session.flush();
			session.close();
		}
    	
    }
    
    
    public boolean hasConfNameExisted(String confName) {
        
    	org.hibernate.classic.Session session = null;
    	boolean existed;
		
		try {
			
			SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
			session = sessionFactory.openSession();
			
			Connection connection = session.connection();
			Statement statement = connection.createStatement();
			ResultSet result;

			//checking if same entry is already in database
			String query = "select tokboxID from tokbox where confName='"+confName+"'";
			result = statement.executeQuery(query);
			
			//yes - ignore; no - add a new email record
			if (result.next() == true) {
				existed = true;
			} else {
				existed = false;
			}

			statement.close();
			result.close();
			
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			session.flush();
			session.close();
		}
    	
		return existed;
    }
    
    
    public boolean update(Tokbox tokbox) {
    	org.hibernate.classic.Session session = null;
		
		try {
			
			SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
			session = sessionFactory.openSession();
			
			org.hibernate.Transaction tx = session.beginTransaction();			
        	session.saveOrUpdate(tokbox);
			tx.commit();
			
			
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			session.flush();
			session.close();
		}
		
		return true;
    	
    }
    
    
    public Tokbox getTokboxByCreator(long creator) throws java.sql.SQLException{
		
    	Tokbox tokbox = null;
		org.hibernate.classic.Session session = null;
		
		try {
			
			SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
			session = sessionFactory.openSession();
			
			Connection connection = session.connection();
			Statement statement = connection.createStatement();
			ResultSet result;
			
			String query = "select * from tokbox where creator='"+creator+"'";
			result = statement.executeQuery(query);
			
			while(result.next()) {
				
				tokbox = new Tokbox();
				tokbox.setTokboxID(result.getLong("tokboxID"));
				tokbox.setCreatorID(result.getString("creatorID"));
				tokbox.setCreatorName(result.getString("creatorName"));
				tokbox.setExpired(result.getString("expired"));
				tokbox.setConfName(result.getString("confName"));
				tokbox.setAttendeesID(result.getString("attendeesID"));
				tokbox.setAttendeesNames(result.getString("attendeesNames"));
				tokbox.setNoteTakerID(result.getString("noteTakerID"));
				tokbox.setNoteTakerName(result.getString("noteTakerName"));
				tokbox.setNotes(result.getString("notes"));
				tokbox.setSessionID(result.getString("sessionID"));
				tokbox.setSessionIDLocal(result.getString("sessionIDLocal"));
				tokbox.setTokenSession(result.getString("tokenSession"));
				tokbox.setTokenPublisher(result.getString("tokenPublisher"));
				tokbox.setTokenSubscriber(result.getString("tokenSubscriber"));
				tokbox.setTokenModerator(result.getString("tokenModerator"));
				
			}

			statement.close();
			result.close();
			//connection.close();
			
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			session.flush();
			session.close();
		}
		
		return tokbox;
	}
    
    
    
    public Tokbox getTokboxByConfName(String confName) throws java.sql.SQLException{
		
    	Tokbox tokbox = null;
		org.hibernate.classic.Session session = null;
		
		try {
			
			SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
			session = sessionFactory.openSession();
			
			Connection connection = session.connection();
			Statement statement = connection.createStatement();
			ResultSet result;
			
			String query = "select * from tokbox where confName='"+confName+"'";
			result = statement.executeQuery(query);
			
			while(result.next()) {
				
				tokbox = new Tokbox();
				tokbox.setTokboxID(result.getLong("tokboxID"));
				tokbox.setCreatorID(result.getString("creatorID"));
				tokbox.setCreatorName(result.getString("creatorName"));
				tokbox.setExpired(result.getString("expired"));
				tokbox.setConfName(result.getString("confName"));
				tokbox.setAttendeesID(result.getString("attendeesID"));
				tokbox.setAttendeesNames(result.getString("attendeesNames"));
				tokbox.setNoteTakerID(result.getString("noteTakerID"));
				tokbox.setNoteTakerName(result.getString("noteTakerName"));
				tokbox.setNotes(result.getString("notes"));
				tokbox.setSessionID(result.getString("sessionID"));
				tokbox.setSessionIDLocal(result.getString("sessionIDLocal"));
				tokbox.setTokenSession(result.getString("tokenSession"));
				tokbox.setTokenPublisher(result.getString("tokenPublisher"));
				tokbox.setTokenSubscriber(result.getString("tokenSubscriber"));
				tokbox.setTokenModerator(result.getString("tokenModerator"));
				
			}

			statement.close();
			result.close();
			
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			session.flush();
			session.close();
		}
		
		return tokbox;
	}


    
    public boolean deleteTokbox(String confName) {
    	
    	Tokbox tokbox;
    	org.hibernate.classic.Session session = null;
		
		try {
			
			SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
			session = sessionFactory.openSession();
			
			tokbox = getTokboxByConfName(confName);
			
			org.hibernate.Transaction tx = session.beginTransaction();			
        	session.delete(tokbox);
			tx.commit();
			

			
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			session.flush();
			session.close();
		}
		
		return true;
    	
    }
    
    
    public ArrayList<Tokbox> getAllConfs() {
    	
    	ArrayList<Tokbox> tokboxes = new ArrayList<Tokbox>();
    	Tokbox tokbox;
		org.hibernate.classic.Session session = null;
		
		try {
			
			SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
			session = sessionFactory.openSession();

			Criteria crit = session.createCriteria(Tokbox.class);
			
			@SuppressWarnings("unchecked")
			List<Tokbox> dbTokboxes = crit.list();

			tokbox = new Tokbox();
			
			for (Iterator<Tokbox> it = dbTokboxes.iterator();it.hasNext();){
				
				tokbox = (Tokbox) it.next();
				tokboxes.add(tokbox);
				
			}
			

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			session.flush();
			session.close();
		}
		
		return tokboxes;
    }
}
